Imports System.Data.OleDb
Imports DTO

Public Class Phong_Dao

    Inherits AbstractDAO

    Public Sub New()

    End Sub

    Public Function LayBang() As DataTable

        Dim dt As New DataTable()
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From Phong"
        ' B4: Thuc thi chuoi strSQL
        Dim da As New OleDbDataAdapter(strSQL, conn)
        da.FillSchema(dt, SchemaType.Source)
        da.Fill(dt)
        ' B5: Dong ket noi CSDL
        conn.Close()
        Return dt

    End Function

    Public Function LayDanhSach() As IList

        Dim ds As New ArrayList()
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From Phong"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()
            Dim phgDto As New Phong_Dto
            phgDto.MaPHG = dr("MaPHG")
            phgDto.SoPhong = dr("SoPhong")
            phgDto.TinhTrang = dr("TinhTrang")
            phgDto.MaLoai = dr("MaLoai")
            'If (phgDto.TinhTrang = "Busy") Then
            ds.Add(phgDto)
            'End If
        End While
        ' B5: Dong ket noi CSDL
        dr.Close()
        conn.Close()
        Return ds

    End Function

    Public Sub CapNhatBang(ByVal dt As DataTable)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From Phong"
        'B4: Thuc thi chuoi strSQL
        Dim da As New OleDbDataAdapter(strSQL, conn)
        Dim cb As New OleDbCommandBuilder(da)
        da.Update(dt)
        ' B5: Dong ket noi CSDL
        conn.Close()

    End Sub

    Public Function TimKiem(ByVal mp As Integer) As Phong_Dto

        Dim phgDto As New Phong_Dto
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From Phong Where MaPHG = ?"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        cmd.Parameters.Add("@MaPHG", OleDbType.Integer)

        cmd.Parameters("@MaPHG").Value = mp

        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()
            phgDto = New Phong_Dto()
            phgDto.MaPHG = dr("MaPHG")
            phgDto.SoPhong = dr("SoPhong")
            phgDto.TinhTrang = dr("TinhTrang")
            phgDto.MaLoai = dr("MaLoai")
        End While
        ' B5: Dong ket noi CSDL
        conn.Close()
        Return phgDto

    End Function

    Public Sub Them(ByVal phgDto As Phong_Dto)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = _
        "Insert into Phong(SoPhong, TinhTrang, MaLoai) values (?, ?, ?)"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        cmd.Parameters.Add("@SoPhong", OleDbType.WChar)
        cmd.Parameters.Add("@TinhTrang", OleDbType.WChar)
        cmd.Parameters.Add("@MaLoai", OleDbType.Integer)

        cmd.Parameters("@SoPhong").Value = phgDto.SoPhong
        cmd.Parameters("@TinhTrang").Value = phgDto.TinhTrang
        cmd.Parameters("@MaLoai").Value = phgDto.MaLoai
        cmd.ExecuteNonQuery()

        strSQL = "Select @@IDENTITY"
        cmd = New OleDbCommand(strSQL, conn)
        phgDto.MaPHG = CInt(cmd.ExecuteScalar())
        ' B5: Dong ket noi CSDL
        conn.Close()

    End Sub

    Public Sub Xoa(ByVal mp As Integer)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Delete From Phong Where MaPHG = ?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        cmd.Parameters.Add("@MaPHG", OleDbType.Integer)

        cmd.Parameters("@MaPHG").Value = mp

        cmd.ExecuteNonQuery()
        ' B5: Dong ket noi CSDL
        conn.Close()

    End Sub

    Public Sub Sua(ByVal phgDto As Phong_Dto)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = _
        "Update Phong Set SoPhong = ? , TinhTrang = ? , MaLoai = ? Where MaPHG = ?"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        cmd.Parameters.Add("@SoPhong", OleDbType.WChar)
        cmd.Parameters.Add("@TinhTrang", OleDbType.WChar)
        cmd.Parameters.Add("@MaLoai", OleDbType.Integer)
        cmd.Parameters.Add("@MaPHG", OleDbType.Integer)

        cmd.Parameters("@SoPhong").Value = phgDto.SoPhong
        cmd.Parameters("@TinhTrang").Value = phgDto.TinhTrang
        cmd.Parameters("@MaLoai").Value = phgDto.MaLoai
        cmd.Parameters("@MaPHG").Value = phgDto.MaPHG

        cmd.ExecuteNonQuery()
        ' B5: Dong ket noi CSDL
        conn.Close()

    End Sub

    Public Function DSPhongRanh(ByVal mlp As Integer) As IList

        Dim ds As New ArrayList()
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From Phong Where MaLoai = ?"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        cmd.Parameters.Add("@MaLoai", OleDbType.Integer)

        cmd.Parameters("@MaLoai").Value = mlp

        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()
            Dim phgDto As New Phong_Dto
            phgDto.TinhTrang = dr("TinhTrang")
            If (phgDto.TinhTrang = "Ready") Then     'Phong san sang phuc vu
                phgDto.MaPHG = dr("MaPHG")
                phgDto.SoPhong = dr("SoPhong")
                phgDto.MaLoai = dr("MaLoai")
                ds.Add(phgDto)
            End If
        End While
        ' B5: Dong ket noi CSDL
        dr.Close()
        conn.Close()
        Return ds

    End Function

    Public Function DSPhongBan(ByVal mlp As Integer) As IList

        Dim ds As New ArrayList()
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From Phong Where MaLoai = ?"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.Parameters.Add("@MaLoai", OleDbType.Integer)

        cmd.Parameters("@MaLoai").Value = mlp

        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()
            Dim phgDto As New Phong_Dto
            phgDto.TinhTrang = dr("TinhTrang")
            If (phgDto.TinhTrang = "Busy") Then     'Phong dang ban
                phgDto.MaPHG = dr("MaPHG")
                phgDto.SoPhong = dr("SoPhong")
                phgDto.MaLoai = dr("MaLoai")
                ds.Add(phgDto)
            End If
        End While
        ' B5: Dong ket noi CSDL
        dr.Close()
        cn.Close()
        Return ds

    End Function
    Public Function TimKiem_SP(ByVal sp As Integer) As Phong_Dto

        Dim pDto As New Phong_Dto
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From Phong Where SoPhong = ?"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.Parameters.Add("@SoPhong", OleDbType.Integer)
        cmd.Parameters("@SoPhong").Value = sp
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()
            pDto = New Phong_Dto()
            pDto.MaPHG = dr("MaPHG")
            pDto.SoPhong = dr("SoPhong")
            pDto.TinhTrang = dr("TinhTrang")
            pDto.MaLoai = dr("MaLoai")
        End While
        ' B5: Dong ket noi CSDL
        cn.Close()
        Return pDto

    End Function

End Class
'Select lp.TenLoaiPHG as TenLoaiPHG, hd.NgayTraPhong as Ngay, sum(hd.TongTien) as DoanhThu
'From LoaiPhong lp, Phong p, PhieuThuePhong pt, HoaDon hd
'Where year(hd.NgayTraPhong) = {?paraNam} and month(hd.NgayTraPhong) = {?paraThang} and pt.MaPT = hd.MaPT and p.MaPHG = pt.MaPHG and lp.MaLoai = p.MaLoai
'Group by lp.TenLoaiPHG, hd.NgayTraPhong